package javaTest03.exercise02;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class JdbcTest {

    public static void main(String[] args) {
        ArrayList<Student> studentArrayList = getStudentList();
        if (studentArrayList != null){
            for (Student student : studentArrayList) {
                System.out.println(student);
            }
        }else {
            System.out.println("数据获取失败!");
        }
    }

    public static ArrayList<Student> getStudentList(){//得到数据库的记录并转换为java对象
        ResultSet resultSet = null;//数据查询结果集
        PreparedStatement statement = null;//sql操作对象
        Connection connection = null;//数据库连接对象
        ArrayList<Student> students = new ArrayList<>();
        try {
            connection = JdbcUtils.getConnection("javaTest03/exercise02/jdbcTest");
            statement = connection.prepareStatement(getSql());
            resultSet = statement.executeQuery();

            while (resultSet.next()){
                String name = resultSet.getString("name");
                int gender = resultSet.getInt("gender");
                int className = resultSet.getInt("className");
                String major = resultSet.getString("major");
                String college = resultSet.getString("college");

                Student student = new Student(name,gender,className,major,college);

                students.add(student);
            }

            return students;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.closeConnection(connection,statement,resultSet);//关闭数据库连接
        }

        return null;
    }

    public static String getSql(){//获取sql语句
        return "SELECT\n" +
                        "    stu.name,stu.gender,cla.className,mj.major,mj.college\n" +
                        "FROM\n" +
                        "    student stu\n" +
                        "join\n" +
                        "    class cla\n" +
                        "on\n" +
                        "    cla.eid = stu.class\n" +
                        "join\n" +
                        "    major mj\n" +
                        "on\n" +
                        "    mj.eid = cla.major\n;";
    }
}
